Due: January 17th, at the beginning of class.
No exceptions!
What to turn in: Please turn in a hard copy of the queries you ran, each query followed by the tuples that you got as an answer.
This homework is a chance for you to get started with SQL Server by using the Query analyzer, as well as to run some simple SQL queries.
To use the Query analyzer you need to follow two steps:
Here is a description of the database tables that I have created for you:
Student(S_SSN,S_Name,S_City,S_Age)
Enrollment(CallNo,S_SSN,Grade)
Offering(CourseNo,CallNo,F_SSN)
Course(CourseNo,Title, CrHrs,Did)
Faculty(F_SSN, F_Name,F_Mail,F_Room,F_Salary)
Deaprtment(Did,D_Name,F_SSN,D_Budget)
All attributes of the Student entity are prefixed by S_, for the faculty by F_.SSN designates social security number, which is a unique identifier for the Student or Faculty. Each department has a unique Department ID (Did, e.g. CSE,EE,HIST), department name, a chair (identified by his/her SSN), and a budget. Courses have a course number, title, number of credits and a department which offers them (e.g. 444,“Intro to Databases”,3,”CSE”). An offering consists of a course number, a call number which uniquely identifies the offering (this is equivalent to the SLN number at UW), and a faculty who teaches that course(identified by F_SSN). The Enrollement has a call number (identifying a particular offering), a student SSN and a grade for that student in that offering.
1. [5] Find the names of all students who live in Seattle. Give the relational algebraic expression for this query
2. [5] For each student, output their name, SSN and GPA. GPA is calculated as the sum of the students grades over the number of courses the student is taking
3. [5] For each city from where there is at least one student, output the average age of the students that come from it. [Hint: S_City column in Student has all cities from where there is at least one student]
4. [10] Find the names of all students who are enrolled in courses entitled Compilers and Databases (students must be enrolled in both to show up in your answer). Give a relational algebraic expression for this query.
5. [10] List all courses (CourseNo and Title) that have at least two students of age 21 or over
6. [10] Find students who are enrolled in every course offered by department with name "InformationSc”
7. [10] Find the names of all faculty who teach more than one course but are not department chairs
8. [20] Report the total number of departments that have at least one course with enrollment of 5 or less but offers no less than 3 courses
9. [20] Find all faculty (entire faculty record) who teaches one or more courses but teaches no course that has an enrollment less than 3
[5] Please answer the following questions: